package com.easyedu.core.dao;

import com.easyedu.core.entity.po.HasArrangeCourse;
import com.easyedu.core.entity.po.OpenCourseTime;
import com.easyedu.core.entity.po.OpenCourseTimeMid;
import java.util.List;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

/**
 * @author lmr
 **/
@Mapper
public interface OpenCourseTimeMidDao {

     /**
      * 功能描述:手动创建开课</br>
      * 数据表：open_course_time(插入), open_course(查询)
      *
      * @param openCoursesId 1 开放课程唯一id
      * @param openWeek      2 开课周
      * @param openDay       3 开课星期
      * @param openLesson    4 开课节次
      * @param classroom     5 开课教室
      * @return : java.lang.Integer
      **/
     @Insert("insert into open_course_time_mid(open_course_time_mid_version_id,open_course_time_mid_version_name,"
             + "open_courses_id,  open_year, semester, open_institute_id, open_institute_name, teach_institute_id,"
             + " teach_institute_name, open_department_id, open_department_name,course_code, course_number, "
             + "course_name, credit, open_week, open_lesson, open_day,classroom, create_time, update_time ) "
             + "select #{versionId},#{versionName},open_courses_id, open_year, semester, "
             + "open_institute_id, open_institute_name,teach_institute_id, teach_institute_name, open_department_id, "
             + "open_department_name, course_code, course_number, course_name, credit, #{openWeek},#{openLesson}, "
             + "#{openDay}, #{classroom}, NOW(), NOW() from open_course where open_courses_id = #{openCoursesId}")
     Integer createFromOpenCourseAndPara(long openCoursesId, long openWeek, long openDay, long openLesson,
                                         String versionId, String versionName, String classroom);

     /**
      * 查询课程开课状态
      *
      * @param openCoursesId
      * @param versionId
      * @return
      */
     @Select("select open_week,open_day,open_lesson from open_course_time_mid where open_courses_id = "
             + "#{openCoursesId} and open_course_time_mid_version_id = #{versionId}")
     List<OpenCourseTimeMid> getArrangeCourseStatus(long openCoursesId, String versionId);

     /**
      * 查询教室被占用情况
      *
      * @param openWeek
      * @param openDay
      * @param openLesson
      * @param classroom
      * @param versionId
      * @return
      */
     @Select("select open_courses_id,course_name from open_course_time_mid where open_week = #{openWeek} and open_day"
             + " = " + "#{openDay} and open_lesson = #{openLesson} and classroom =  #{classroom} and  "
             + "open_course_time_mid_version_id = #{versionId}")
     OpenCourseTime getByClassroom(long openWeek, long openDay, long openLesson, String classroom, String versionId);

     @Update("delete from open_course_time_mid where  course_number  =  #{courseNumber} and "
             + "open_course_time_mid_version_id = #{versionId}")
     Integer cancelOpenCourse(String courseNumber, String versionId);

     /**
      * 获取老师所教的课程
      *
      * @param teacherId
      * @param openYear
      * @param semester
      * @param versionId
      * @return
      */
     @Select("select * from open_course_time_mid where open_courses_id in( select open_courses_id from open_course "
             + "where "
             + "teacher_id = #{teacherId} and open_course.open_year = #{openYear} and open_course.semester = "
             + "#{semester}) and open_course_time_mid_version_id = #{versionId}")
     List<OpenCourseTime> getTeacherCourse(long teacherId, long openYear, String semester, String versionId);

     /**
      * 获取已排课教室状态
      *
      * @param openYear
      * @param semester
      * @return
      */
     @Select("select classroom,open_week,open_day,open_lesson from open_course_time_mid where open_year = #{openYear} "
             + "and semester = #{semester} and open_course_time_mid_version_id = #{versionId}  group by classroom,"
             + "open_week," + "open_day,open_lesson")
     List<OpenCourseTime> getClassRoomStatus(int openYear, String semester, String versionId);

     /**
      * 根据版本获取排课状态
      * @param versionId 版本id
      * @return 排课中间版本课程列表
      */
     @Select("select * from open_course_time_mid where open_course_time_mid_version_id = #{versionId} ")
     List<OpenCourseTimeMid> getByVersionId(String versionId);

     /**
      * 在课程中间表中根据版本id获取排课状态
      * @param openYear 开课年份
      * @param semester 开课学期
      * @param versionId 版本id
      * @return 课程中间信息
      */
     @Select("select open_courses_id,open_week,open_lesson,open_day from open_course_time_mid where open_year = "
             + "#{openYear} and semester = #{semester} and open_course_time_mid_version_id = #{versionId} group by "
             + "open_courses_id, open_week, open_lesson, open_day ")
     List<OpenCourseTimeMid> getOpenCourseTime(int openYear, String semester, String versionId);

     /**
      * 中间排课版本信息
      * @param openYear 开课年
      * @param semester 开课学期
      * @param building 开课建筑
      * @param floor 开课楼层
      * @param week 开课周
      * @param classroom 开课教室
      * @param versionId 开课版本
      * @return 开课信息列表
      */
     @Select("select o.course_number, o.course_name, oc.teacher_name, o.open_week, o.open_day, o.open_lesson,"
             + "b.building_id, b.build_name, c.classroom_id, c.classroom_name, c.floor "
             + "from open_course_time_mid o, building_address b, classroom c, open_course oc "
             + "where "
             + "b.build_name = c.belong_build and o.classroom = c.classroom_name and "
             + "oc.open_courses_id = o.open_courses_id and "
             + "o.open_year = #{openYear} and "
             + "o.semester = #{semester} and "
             + "o.open_week= #{week} and "
             + "b.build_name = #{building} and "
             + "c.floor = #{floor} and "
             + "o.classroom = #{classroom} and "
             + "o.open_course_time_mid_version_id = #{versionId}")
     List<HasArrangeCourse> getHasArrangeCourse(int openYear, String semester, String building, int floor,
             int week, String classroom, String versionId);

     /**
      * 根据版本获获取占用教室列表
      * @param openYear 开课年
      * @param semester 开课学期
      * @param versionId 开课版本
      * @return 教室列表
      */
     @Select("select classroom from open_course_time_mid where open_year = #{openYear} and semester = #{semester} and "
             + "open_course_time_mid_version_id = #{versionId} group by classroom")
     List<String> getOpenCourseClassRoom(int openYear, String semester, String versionId);
}
